注意：所有文章除特别说明外，转载请注明出处.
MySQL - 索引优化
[TOC]
通过索引可以帮助我们解决大多数的SQL性能问题。
MySQL索引类比是一本书前面的目录，能加快数据库的查询速度。
在没有索引的情况下，数据库会遍历全部数据后选择符合条件的。而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。
提示：索引最大的优势在于将无序的数据变成有序的数据(相对)。
索引的数据结构
二叉树
因为在二叉搜索树中，左边的节点值比根节点值小，右边节点值比根节点值大，同时左右子节点都是排序树。
- 优点，可以解决大量数据索引无法一次加载进内存中问题，二叉搜索树可以批量加载数据进内存
- 缺点，检索时间与树的高度有关，树的高度越高，检索次数及时间相对就会越久。极端情况下，如果数据本身就是有序的，二叉搜索树会退化成链表，性能会急剧降低。
红黑树
红黑树是一种自平衡二叉树，主要解决二叉搜索树在极端情况下退化为链表的情况，在数据插入的时候同时调整整个树，使得其节点尽量均匀分布，保持平衡性，目的在于降低树的高度，提高查询效率。
- 优点，解决二叉搜索树的极端情况的退化问题
- 缺点，检索时间依旧与树的高度有关，当数据量很大时，树的高度就会很高，检索的次数就比较多，检索的时间会比较久，效率低。
哈希表
通过一定的算法计算数据的Hash值，然后得到数据的存放位置，例HashMap采用就是这种数据索引结构。
- 优点，检索时间快，平均检索时间为O(1)。
- 缺点，因为哈希值是通过算法计算出来的，存在Hash碰撞的可能，如HashMap对于Hash值相同的数据，会在Hash值所在桶创建一个链表，用于存放相同Hash值的数据。在数据量很大的情况下，内存无法加载全部的数据索引。
B树
B树是一种多路搜索树，每个子节点可以拥有多于2个子节点，M路的B树最多可拥有M个子节点。设计成多路，其目的是为了降低树的高度，降低查询次数，提高查询效率。
1. 叶节点具有相同的深度（通常为3-5）
2. 叶节点的指针为空
3. 节点中的数据索引从左到右递增排序
B+树
B树是一种多路搜索树，每个子节点可以拥有多于2个子节点，M路的B树最多可拥有M个子节点。设计成多路，其目的是为了降低树的高度，降低查询次数，提高查询效率。
1. 非叶子节点不存储data，只存储索引，可以放更多的索引
2. 叶子节点不存储指针
3. 顺序访问指针，提高区间访问的性能
B树与B+树的区别
1. B+Tree中的非叶子结点不存储数据，只存储键值
2. B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址
3. B+Tree的每个非叶子节点由n个键值key和n个指针point组成
1.索引类型 normal | unique | full text
1.normal 普通索引
2.unique 唯一索引，不允许重复的索引
3.full text 表示全文搜索的索引
2.索引的存储分类
在MySQL中索引使用的数据结构主要有btree索引和哈希索引。
1 B树索引
MySQL中的btree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎实现的方式是不同的。
MyISAM
B+Tree叶节点的data域存放的是数据记录的地址。
在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB
其数据文件本身就是索引文件。
相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。
这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。
在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。
因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
2 哈希索引
哈希索引，底层的数据结构是哈希表，因此绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能快。其余大部分场景使用btree索引。
哈希索引不适用于范围查找。
3 R树索引（空间索引）
空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
4 全文索引(full text)
全文索引是MyISAM的一种特殊类型，主要用于全文索引。
MySQL索引
本部分细节都是基于MySQL的InnoDB引擎。我们清楚，索引类似于字典的目录，可以提高查询的效率。
1 索引的优点
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
2 索引的缺点
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
3 索引的分类
1.普通索引：基本的索引，其没有任何的限制
2.唯一索引：与普通索引类似，不同的是MySQL数据库索引列的值必须唯一，但允许有空值。
3.主键索引：特殊的唯一索引，不允许有空值。
4.全文索引：表示全文搜索的索引。
5.单列索引和多列索引
单列索引：表示只有一个字段的方式。
多列索引：
注意：索引使用原则：1.选择唯一索引。 2.为经常需要排序 | 分组 | 联合操作的字段建立索引。3.为常作为查询条件的字段建立索引。4.限制索引的数目。5.尽量使用数据量少的索引。6.尽量使用前缀索引。7.删除不再使用或者很少使用的索引。8.不推荐同一列建立多个索引。9.经常更新或者修改的列不要建立索引。
1.最左前缀原则
在一张表中依据name字段来建立索引，采用B+树的结构。然后进行模糊查询。SQL语句如下所示：
select id from table where name like '张%'
这样这段SQL语句就会根据模糊查询对表进行向右的查询，直到没有满足条件的为止。所以这种从左至右的查询原则就是最左前缀原则。
2.B+树做索引，而非哈希表做索引原因
1.哈希表是将索引字段映射成对应的哈希码然后存放在对应的位置，这样的话如果要进行模糊查询的话显然哈希表这种结构是不支持的，只能遍历这个表。B+树可以通过最左前缀原则快速找到对应的数据。
2.哈希表不支持范围查找，只能遍历全表。
3.索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码，则形成的索引结构会是一条很长的链表，查找的时间会大大增加。
3.主键索引与非主键索引的区别
非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据。其中非主键索引也被称为二级索引，主键索引被称为聚簇索引。
4.使用主键自增索引原因
如果使用的主键是自增的，那么每次插入的ID都会比前面的大，那么每次只需要在后面插入就行了，不需要移动位置和分裂操作等，这样能够提高性能。
5.索引优化策
1.不在索引列上进行运算或使用函数
因为在列上进行运算或者使用函数会使索引失效，从而进行全表扫描。
//这样会走索引
select * from table where id = '4'
//这样会走全表扫描
select * from table where id + 1 = 4
2.注意隐式类型转换
这里假设id为varchar类型：
//走全表扫描
select * from table where id = 100
//走索引扫描
select * from table where id = '100'
这里是因为隐式类型转换在索引字段上做了函数操作，所以会进行全表扫描。
3.前导模糊查询不会用到索引
%李、%李% 这样都会导致全表扫描，非前导模糊查询可以使用索引。
6.选择合适的列建立索引
1.在where从句，group by从句，order by从句，on从句中出现的列。
2.索引字段越小越好。
3.离散度大的列放到联合索引的前面：select * from payment where staff_id = 2 and customer_id = 584; 在这里建立索引是：(index(staff_id, customer_id)) 还是：index(customer_id, staff_id) 在这里因为customer_id离散度更大，所以应该使用index(customer_id, staff_id)。
7.索引的维护及优化 重复及冗余索引
重复索引表示相同的列以相同的顺序建立同类型的索引，例如：在id为主键的时候，建立id索引和主键索引就是重复的索引。
1.查找重复及冗余索引
使用 pt-duplicate-key-checker 工具检查重复及冗余索引 需要的参数是 用户名和密码
2.删除不用的索引
通过使用 pt-index-usage 工具来进行索引使用情况的分析
SQL优化
MySQL慢查日志对有效率问题的SQL进行监控
//1.查看数据库是否开启检查日志
show variable like 'slow_query_log'
set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log'
通过show processlist 命令查看当前MySQL进行的线程
show processlist
通过show status命令了解各种SQL的执行频率
show [session | global] status
通过命令 show profile 分析SQL
show profiles | show profile
通过命令 trace 分析优化器如何选择执行计划
1. 打开trace，设置格式为JSON，设置trace能够使用的最大大小
Count()和Max()优化
max()函数
max()函数：如：查询最后支付时间 select max(payment_date) from payment;
优化：可以在表payment上建立一个索引 idx_paydate 在列 payment_date 。从而通过索引大大的减少了SQL执行时间，提高效率。
count()函数
count()函数：如：在一条sql中同时查出2005年和2006年电影的数量 select COUNT(release_year='2006' or null) as '2006年电影数量', count(release_year='2005' or null) as '2005年电影数量' from film;
注意：count()和count(id)查询的值是不一样的，count()查询的值会包括null值。
子查询优化
通常情况下，需要将子查询优化为join查询，但是在优化时要注意关联键是否有一对多的关系，需要注意重复数据。如果存在重复的数据，使用 distinct 命令去掉重复的数据。
explain select title, release_year, length from film where film_id IN(
select film_id from film_actor where actor_id IN(
select actor_id from actor where first_name = 'Aaron'
)
)
GROUP BY优化
如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL禁止排序。
优化前：
explain select actor.first_name, actor,last_name, COUNT(*) FROM sakia.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY film_actor.actor_id;
优化后：
explain select actor.first_name, actor.last_name, c.cnt from sakila.actor INNER JOIN(SELECT actor_id, COUNT(*) AS cnt from sakila.film_actor GROUP BY actor_id) AS c USING(actor_id);
limit 优化
limit常用于分页处理，经常会伴随order by从句使用，因此大多数时候会使用filesorts 这样会造成大量的 IO 问题。
select film_id, description from sakila.film order by title limit 50, 5;
1.使用有索引的列或主键进行 order by 操作
select film_id, description from saklia.film order by film_id limit 50, 5;
2.记录上次返回的主键，在下次查询时使用主键过滤
select film_id, description from saklia.film where film_id > 55 and film_id <= 60 order by film_id limit 1, 5;
注意：这里避免数据量大是扫描过多的记录。
OR优化
在含有OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引。
优化分页查询
在一般分页查询时，通过创建覆盖索引能够比较好的提高性能
SQL技巧
正则表达式
^ 字符串开始处进行匹配
$ 字符串末尾处进行匹配
. 匹配任意单个字符
[...] 匹配出括号内的任意字符
[^...] 匹配非括号内的任意字符
a* 匹配零个或多个a字符（包括空串）
a+ 匹配1个或多个a（不包括空串）
a? 匹配1个或零个a
a1 | a2 匹配a1或a2
...
